﻿
CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVertical] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*,rf.Denumire AS DenRegim
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 

(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
  sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
  SUM(ValoareTVA) As ValoareTVA,
  dd.CotaTVA,dd.IDRegimFiscal
   FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
   GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal) docd ON doc.IDDocument = docd.IDDocument
JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal   
WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END